﻿
namespace Gola.VN.Scb.Web.Bll
{
    class QueryConstants
    {
        #region provider
        public class Provider
        {
            public static string Insert
            {
                get
                {
                    return "Insert into Provider({0}, {1}, {2}, {3}, {4}, {5}, {6}) " +
                        string.Format("values(@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6})",
                        ExpressionId, Name, DisplayName, Address, Email, PhoneNumber, Logo);
                }
            }

            public static string Update
            {
                get
                {
                    return string.Format("Update Provider set " +
                        "{0} = @{0}, {1} = @{1}, {2} = @{2}, {3} = @{3}, {4} = @{4} " +
                        "Where {5} = @{5}",
                        DisplayName, Address, Email, PhoneNumber, Logo, Name);
                }
            }

            public static string GetProviders
            {
                get
                {
                    return
                        @" SELECT  P.Id ,
                                    P.Name ,
                                    P.DisplayName ,
                                    P.Email ,
                                    P.PhoneNumber ,
                                    P.Logo ,
                                    L.Address ,
                                    L.LocationId ,
                                    L.Latitude ,
                                    L.Longtitude ,
                                    L.Title
                            FROM    dbo.Provider P
                                    LEFT JOIN dbo.Location L ON P.Id = L.ProviderId
                            ORDER BY CAST(P.Id AS VARCHAR(20))";
                }
            }
            public const string ExpressionId = "ExpressionId";
            public const string Name = "Name";
            public const string DisplayName = "DisplayName";
            public const string Address = "Address";
            public const string Email = "Email";
            public const string PhoneNumber = "PhoneNumber";
            public const string Logo = "Logo";
        }
        #endregion

        #region category
        public class Category
        {
            public static string GetCategoriesAsTree
            {
                get
                {
                    return string.Format("select {0}, {1}, {2}, {3} from category order by cast({0} as nvarchar)",
                        Id, Name, Notes, ParentId);
                }
            }

            public const string Id = "ID";
            public const string Name = "NAME";
            public const string Notes = "NOTES";
            public const string ParentId = "PARENTID";
        }
        #endregion

        #region product
        public class Product
        {
            public static string Insert
            {
                get
                {
                    return string.Format("insert into Product({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}) values(@{0}, " +
                        "@{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}, @{8})",
                        ProviderId, CategoryId, Title, Description, Image, Price, Currency, Promotion, OldPrice);
                }
            }

            public const string ProviderId = "ProviderId";
            public const string CategoryId = "CategoryId";
            public const string Title = "Title";
            public const string Description = "Description";
            public const string Image = "Image";
            public const string Price = "Price";
            public const string Currency = "Currency";
            public const string Promotion = "Promotion";
            public const string OldPrice = "OldPrice";

            public const string GetAll = @"SELECT [PRODUCT].[ID]
                                      ,[PRODUCT].[PROVIDERID]
                                      ,[PRODUCT].[CATEGORYID]
                                      ,[PRODUCT].[CRAWLLINKID]
                                      ,[PRODUCT].[TITLE]
                                      ,[PRODUCT].[DESCRIPTION]
                                      ,[PRODUCT].[IMAGE]
                                      ,[PRODUCT].[LINK]
                                      ,[PRODUCT].[PRICE]
                                      ,[PRODUCT].[OLDPRICE]
                                      ,[PRODUCT].[CURRENCY]
                                      ,[PRODUCT].[PROMOTION]
                                      ,[PRODUCT].[CREATEDDATE]
                                      ,[PRODUCT].[PRODUCTID]
                                      ,[PROVIDER].[NAME] AS PROVIDERNAME
                                      ,[PROVIDER].[ADDRESS] AS PROVIDERADDRESS
                                  FROM [PRODUCT] 
                                  INNER JOIN [PROVIDER] ON PROVIDER.ID = PRODUCT.PROVIDERID";
        }
        #endregion
    }
}
